package cz.acies.genea.data.sync;

import java.io.File;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.collections4.MapUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.gedcom4j.model.Family;
import org.gedcom4j.model.FamilySpouse;
import org.gedcom4j.model.Gedcom;
import org.gedcom4j.model.Individual;
import org.gedcom4j.model.IndividualEvent;
import org.gedcom4j.model.IndividualEventType;
import org.gedcom4j.model.PersonalName;
import org.gedcom4j.parser.GedcomParser;
import org.josql.Query;
import org.josql.QueryResults;

import cz.acies.data.DBConnector;
import cz.acies.data.dao.DefaultDAO;
import cz.acies.data.dao.DefaultMapListHandler;
import cz.acies.data.dao.DefaultRowProcessor;
import cz.acies.genea.data.dao.GedFamilyDAO;
import cz.acies.genea.data.dao.GedPersonDAO;
import cz.acies.genea.data.persist.GedFamily;
import cz.acies.genea.data.persist.GedPerson;
import cz.acies.genea.data.persist.GedProject;
import cz.acies.uniql.metadata.DBMetaData;
import cz.acies.uniql.sql.InsertQuery;
import cz.acies.uniql.sql.SelectQuery;
import cz.acies.uniql.sql.UpdateQuery;
import cz.acies.uniql.sql.element.UniQLCondition;
import cz.acies.uniql.sql.element.UniQLWhere;
import cz.acies.uniql.sql.functions.SQLFunc;
import cz.acies.utils.DateUtils;
import cz.acies.utils.StringUtils;
import cz.acies.utils.UIDGenerator;

public class Gedcom4jImport
{

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		try {
			/*
			// HOME
			String jdbcUrl = "jdbc:mysql://localhost:3306/gedcom";
			String jdbcDriver = "org.gjt.mm.mysql.Driver";
			
			Properties props = new Properties();
			props.setProperty("user", "root");
			props.setProperty("password", "smart");
			props.setProperty("useUnicode", "true");
			props.setProperty("characterEncoding", "UTF8");
			props.setProperty("characterSetResults", "UTF8");
			*/
			// WWORK
			String jdbcUrl = "jdbc:mysql://localhost:3305/gedcom";
			String jdbcDriver = "org.gjt.mm.mysql.Driver";

			Properties props = new Properties();
			props.setProperty("user", "root");
			props.setProperty("password", "");
			props.setProperty("useUnicode", "true");
			props.setProperty("characterEncoding", "UTF8");
			props.setProperty("characterSetResults", "UTF8");
			
			String dialectClass = "cz.acies.uniql.dialect.MySQLDialect"; 
			
			DBConnector connector = DBConnector.instance();
			connector.setUrl(jdbcUrl);
			connector.setDriver(jdbcDriver);
			connector.setDialectClass(dialectClass);
			connector.setProps(props);
			connector.initialize();

			Gedcom4jImport inst = new Gedcom4jImport();
			inst.setProjectId("GE.P.000000000000001");
			inst.sync();
		} catch (Exception exc) {
			exc.printStackTrace();
		}
	}

	private String sourceFileId;
	private String projectId;

	private Map<String, String> syncIdRegGC;
	private Map<String, String> syncIdRegGE;

	protected QueryRunner qr;

	public void sync() {
		try {
			GedFamilyDAO fDao = new GedFamilyDAO();
			GedPersonDAO pDao = new GedPersonDAO();
			
			GedcomParser gp = new GedcomParser();
			gp.load("" + getSourceFile().getPath());
			Gedcom g = gp.gedcom;
			
			GedProject pjt = new GedProject(this.getProjectId(), null);
			//pjt.setFamilies(fDao.selectAll());
			//pjt.setPeople(pDao.selectAll());
			
			
			Query q = new Query ();
			q.parse ("SELECT * FROM cz.acies.genea.data.persist.GedFamily");
			//QueryResults qr = q.execute (pjt.getFamilies());
			//List res = qr.getResults ();
			//System.out.println(res);
			/*
			pjt.setPeople(pDao.selectByWhere("person", 
				new UniQLWhere().add(new UniQLCondition("projectId", "=", "''")), GedPerson.class));
			*/
			//System.out.println(g.header.submitter.name);
			
			syncIdRegGC = dbSyncData();
			syncIdRegGE = MapUtils.invertMap(syncIdRegGC);
			
			Map<String, GedFamily> families = new LinkedHashMap<String, GedFamily>(); 
			Map<String, GedPerson> people = new LinkedHashMap<String, GedPerson>();
			
			for (Family f : g.families.values()) {
				String xref = f.xref.replaceAll("@", "");
				String status = (syncIdRegGC.containsKey(xref)) ? "U" : "N";
				
				Map<String, Object> params = new HashMap<String, Object>();
				params.put("id", "'"+xref+"'");
				params.put("status", "'"+status+"'");
				params.put("datelastUpdate", new SQLFunc("NOW"));
				
				GedFamily family = new GedFamily();
				if (status.equals("U")) {
					String id = ""+syncIdRegGC.get(xref);
					updateSyncData(id, xref, params);
					family.setId(id);
					family.setProjectId(getProjectId());
					if (fDao.existsRecord(id)) {
					} else {
						fDao.insert(family);
					}
				} else {
					String id = UIDGenerator.createUID("GE.F");
					family.setId(id);
					family.setProjectId(getProjectId());
					params.put("status", "'S'");
					insertSyncData(id, xref, params);
					fDao.insert(family);
				}
				families.put(""+xref, family);
				
				if (f.husband != null) {
					xref = f.husband.xref.replaceAll("@", "");
					status = (syncIdRegGC.containsKey(xref)) ? "U" : "N";
					String id  = "";
					GedPerson person = new GedPerson();
					if (status.equals("U")) {
						id = ""+syncIdRegGC.get(xref);
						person.setId(id);
						if (!pDao.recordExists(id)) {
							id = UIDGenerator.createUID("GE.R");
							params = new HashMap<String, Object>();
							updateSyncData(id, xref, params);
							person.setProjectId(getProjectId());
							id = pDao.insert(person);
						} 
					} else {
						id = UIDGenerator.createUID("GE.R");
						params = new HashMap<String, Object>();
						params.put("status", "'"+status+"'");
						insertSyncData(id, xref, params);
						person.setId(id);
						person.setProjectId(getProjectId());
						id = pDao.insert(person);
					}
					family.setMan(person);
					fDao.update(family);
					
					people.put(""+xref, person);
				}
				if (f.wife != null) {
					xref = f.wife.xref.replaceAll("@", "");
					status = (syncIdRegGC.containsKey(xref)) ? "U" : "N";
					String id  = "";
					GedPerson person = new GedPerson();
					if (status.equals("U")) {
						id = ""+syncIdRegGC.get(xref);
						person.setId(id);
						if (!pDao.recordExists(id)) {
							id = UIDGenerator.createUID("GE.R");
							params = new HashMap<String, Object>();
							updateSyncData(id, xref, params);
							person.setProjectId(getProjectId());
							id = pDao.insert(person);
						}
					} else {
						id = UIDGenerator.createUID("GE.R");
						params = new HashMap<String, Object>();
						params.put("status", "'"+status+"'");
						insertSyncData(id, xref, params);
						person.setId(id);
						person.setProjectId(getProjectId());
						id = pDao.insert(person);
					}
					family.setWoman(person);
					fDao.update(family);
					
					people.put(""+xref, person);
				}
				
				for (Individual i : f.children) {
					xref = i.xref.replaceAll("@", "");
					status = (syncIdRegGC.containsKey(xref)) ? "U" : "N";
					
					String id = "";
					GedPerson person = new GedPerson();
					
					params = new HashMap<String, Object>();
					params.put("status", "'"+status+"'");
					if (status.equals("U")) {
						id = ""+syncIdRegGC.get(xref);
						person.setId(id);
						if (!pDao.recordExists(id)) {
							id = UIDGenerator.createUID("GE.R");
							params = new HashMap<String, Object>();
							updateSyncData(id, xref, params);
							person.setProjectId(getProjectId()); 
							id = pDao.insert(person);
						}
					} else {
						id = UIDGenerator.createUID("GE.R");
						params = new HashMap<String, Object>();
						params.put("status", "'"+status+"'");
						insertSyncData(id, xref, params);
						person.setId(id);
						person.setProjectId(getProjectId()); 
						id = pDao.insert(person);
					}
					
					people.put(""+xref, person);
				}	
			}
			
			for (Individual i : g.individuals.values()) {
				String xref = i.xref.replaceAll("@", "");
				String status = (syncIdRegGC.containsKey(xref)) ? "U" : "N";
				String id = "";
				
				Map<String, Object> params = new HashMap<String, Object>();
				params.put("status", "'"+status+"'");
				if (status.equals("U")) {
					id = ""+syncIdRegGC.get(xref);
					params.put("status", "S");
					updateSyncData(id, xref, params);
				} else {
					id = UIDGenerator.createUID("GE.R");
					params.put("status", "'N'");
					insertSyncData(id, xref, params);
				}
				
				GedPerson person = new GedPerson();
				if (people.containsKey(xref)) {
					person = people.get(xref);
				}
				person.setProjectId(projectId);
				
				for (PersonalName name : i.names) {
					String firstName = (name.givenName != null) ? name.givenName.value : null;
					String lastName = (name.surname != null) ? name.surname.value : null;
					if (firstName == null) {
						lastName = this.parseMaidenName(i.formattedName());
					}
					
					person.setFirstName(firstName);
					person.setLastName(lastName);
					System.out.println(firstName + " " + lastName);
				}
				person.setGender((i.sex != null) ? i.sex.trim().toUpperCase() : "");
				person.setMaidenName(parseMaidenName(i.formattedName()));
				for (IndividualEvent e : i.events) {
					if (e.type.equals(IndividualEventType.BIRTH)) {
						java.util.Date date = parseDate(""+e.date);
						person.setBirthDate(date);
						person.setBirthPlace((e.place != null) ? e.place.placeName : "");
					}
					if (e.type.equals(IndividualEventType.DEATH)) {
						java.util.Date date = parseDate(""+e.date);
						person.setDeathDate(date);
						person.setDeathPlace((e.place != null) ? e.place.placeName : "");
					}
				}
				System.out.println(person);
				
				if (!pDao.recordExists(id)) {
					id = pDao.insert(person);
				} else {
					pDao.update(person);
				}
				
				people.put(""+xref, person);
			}
					
		} catch (Exception exc) {
			exc.printStackTrace();
		}
	}

	protected void insertSyncData( String id, String xref, Map<String, Object> params) throws Exception {	
		params.put("id", "'"+xref+"'");
		params.put("connectId", "'"+id+"'");
		params.put("datelastUpdate", new SQLFunc("NOW"));
		
		InsertQuery sql = new InsertQuery(dbmd())
		.addFrom("sync_gedcom")
		.addParams(params);
		qr().update(sql.toSQLString());
		
		syncIdRegGC.put(""+xref, ""+id);
		syncIdRegGE.put(""+id, ""+xref);
	}
	
	protected void updateSyncData( String id, String xref, Map<String, Object> params) throws Exception {
		qr().update(new UpdateQuery(dbmd())
			.addFrom("sync_gedcom")
			.addParam("id", ""+xref)
			.addParam("connectId", "'"+id+"'")
			.addParam("status", "S")
			.addParam("datelastUpdate", new SQLFunc("NOW"))
			.add(new UniQLCondition("sync_gedcom.id", "=", "'"+xref+"'"))
			.toSQLString()
		);
	}
	
	// -----  -----------------------------------------------------------------
	
	protected Map<String, String> dbSyncData() {
		Map<String, String> data = new HashMap<String, String>();
		try {
			//QueryRunner qr = new QueryRunner(DBConnector.instance().getDataSource());
			SelectQuery sql = new SelectQuery(DBConnector.instance().dbmd()).addFrom("sync_gedcom");
			List<Map<String, Object>> list = qr().query(sql.toSQLString(), new DefaultMapListHandler(new DefaultRowProcessor()));
			for (Map<String, Object> row : list) {
				String id = ""+row.get("id");
				String connectId = ""+row.get("connectId");
				data.put(id, connectId);
			}
		} catch (Exception exc) {
			exc.printStackTrace();
		}
		return data;
	}

	protected QueryRunner qr() {
		if (qr == null) { qr = new QueryRunner(ds()); }
		return qr;
	}
	
	protected DBMetaData dbmd() { return DBConnector.instance().dbmd(); }
	
	protected DataSource ds() {	return DBConnector.instance().getDataSource();	}
	
	// -----  -----------------------------------------------------------------
	
	private static List<String> months = Arrays.asList(new String[] { "JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC" });
	
	private java.util.Date parseDate(String str) throws Exception {
		java.util.Date date = java.sql.Date.valueOf("2099-01-01");
		if (str != null) {
			String[] as = str.split(" ");
			String ds = "";
			if (as.length >= 3) {
				String d = as[0];
				String m = as[1];
				String y = as[2];
				int mm = months.indexOf(m);
				ds = y + "-" + StringUtils.leftPad(""+(mm+1), 2, "0")+"-"+ StringUtils.leftPad(d, 2, "0");
				date = DateUtils.parseDate(ds, "yyyy-MM-dd");
			}
		}
		return date;
	}
	
	private String parseMaidenName(String str) {
		String s = "";
		if (str == null) return s;
		int pos = str.indexOf("/");
		if (pos > -1) { s = str.substring(pos); }
		s = s.replaceAll("/", "").trim();
		return s;
	}
	
	// -----  -----------------------------------------------------------------

	public File getSourceFile() {
		String dir = "d:/java/repository/acies/genea/UR.M.000000000000001";
		//String fn = "Dytrych-Jaroslav-201312130335.ged";
		String fn = "MH-Dytrych.Jaroslav.201312150719.GED";
		String path = dir+"/"+fn;
		File srcfile = new File(""+path);
		//File srcfile = new File("y:/backup/gg-disk/data/genea/Dytrych.Jaroslav.201309040727.ged");
		return srcfile;
	}

	public String getSourceFileId() {
		return sourceFileId;
	}

	public void setSourceFileId(String sourceFileId) {
		this.sourceFileId = sourceFileId;
	}

	public String getProjectId() {
		return projectId;
	}

	public void setProjectId(String projectId) {
		this.projectId = projectId;
	}

}
